package com.glsc.ngateway.common.base.repo.mysql.gateway.platform.it.project;

import com.glsc.ngateway.common.base.domain.mysql.gateway.platform.it.project.Project;
import com.glsc.ngateway.common.api.platform.dto.it.project.ProjectDto;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;

/**
 * @author xuchang
 */
@Repository
public interface ProjectRepo extends JpaRepository<Project, Long> {

    /***
     * 通过项目编号查询
     * @param projectNo
     * @return
     */
    Project findAllByProjectNo(@NotBlank String projectNo);

    /**
     * 分页查询，带排序
     *
     * @return
     */
    @Query(value = "select * from t_gw_it_project h " +
            "where deleted = 0 " +
            "and if( :#{#pro.projectNo} is not null, h.project_no like CONCAT('%', :#{#pro.projectNo}, '%'), 1=1 ) " +
            "and if( :#{#pro.projectName} is not null, h.project_name like CONCAT('%', :#{#pro.projectName}, '%'), 1=1 ) " +
            "and if( :#{#pro.projectType} is not null, h.project_type = :#{#pro.projectType}, 1=1 ) " +
            "and if( :#{#pro.implmentsSubject} is not null, h.implments_subject = :#{#pro.implmentsSubject}, 1=1 ) " +
            "and if( :#{#pro.importanceLevel} is not null, h.importance_level = :#{#pro.importanceLevel}, 1=1 ) " +
            "and if( :#{#pro.projectManager} is not null, h.project_manager = :#{#pro.projectManager}, 1=1 ) " +
            "and if( :#{#pro.managerDepartment} is not null, h.manager_department = :#{#pro.managerDepartment}, 1=1 ) " +
            "and if( :#{#pro.contactPeople} is not null, h.contact_people = :#{#pro.contactPeople}, 1=1 ) " +
            "and if( :#{#pro.contactDepartment} is not null, h.contact_department = :#{#pro.contactDepartment}, 1=1 ) " +
            "", nativeQuery = true)
    Page<Project> page(Pageable pageable, @NotNull @Param("pro") ProjectDto pro);

    @Query(value = "select count(*) from t_gw_it_project h " +
            "where deleted = 0 " +
            "and if( :#{#id} is not null, h.id != :#{#id}, 1=1 ) " +
            "and ( " +
            "   if( :#{#projectName} is not null, h.project_name = :#{#projectName}, 1=1 ) " +
            "   or if( :#{#projectNo} is not null, h.project_no = :#{#projectNo}, 1=1 ) " +
            ") " +
            "", nativeQuery = true)
    Integer countByIdNotAndNameAndNo(@Param("id") Long id, @Param("projectName") String projectName, @Param("projectNo") String projectNo);
}
